\documentclass{report}
\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage{textcomp}
\usepackage{geometry}
\geometry{top=15mm, bottom=15mm, left=1cm, right=1cm}
\usepackage[french]{babel}
\usepackage{pslatex}
\usepackage[colorlinks=true,urlcolor=black]{hyperref}
\usepackage{graphicx}
\usepackage{fancyhdr}
\pagestyle{fancy}
\renewcommand{\footrulewidth}{0.4pt}
\fancyfoot[L]{Uubu.fr}
\fancyfoot[R]{mar. mai 4 01:59:21 CEST 2010}
\lfoot{\Large \textit{Uubu.fr}}
\hypersetup{pdfinfo={
Title={MySQL - concept général},
Author={Sylvain Girod},
Creator={Bash script from uubu.fr's format version 1},
Producer={Bash script and PDFLaTeX},
Subject={Système de gestion de base de données},
CreationDate={D:20100504015921},
ModDate={D:20100504015921},
Keywords={mysql;db;databases}
}}
\begin{document}
 
\begin{center}
{\Huge MySQL - concept général }
\end{center}
\vspace{1cm}
 
\begin{flushleft}
 
{\large Système de gestion de base de données}
 
\vspace{10mm}
 
\hspace{1mm}  \textbf{MySQL} est un système de gestion de base de données, un serveur de bases de données relationnelles. Une base de données est un ensemble organisé de données. SQL signifie « Structured Query Language »
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Emplacement des fichiers }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} /usr/bin]{Programmes clients}
\item[\hspace{1mm} /usr/sbin]{serveur mysqld}
\item[\hspace{1mm} /var/lib/mysql]{Fichiers de log et bases de données}
\item[\hspace{1mm} /usr/share/doc/packages]{Documentation}
\item[\hspace{1mm} /usr/share/mysql]{Fichiers de messages d'erreurs et jeux de caractères}
\item[\hspace{1mm} sql-bench]{Suites de tests}
\end{description}
 
\vspace{1cm}{\huge Comptes par défaut }
 
\
 
\hspace{1mm}  Le script \textbf{mysql\_install\_db} démarre le serveur mysqld et initialise les tables de droits, avec les paramètres suivants:
\vspace{5mm} \\
\hspace{1mm}  Deux comptes MySQL \textbf{root} sont créés en tant qu'administrateurs ayant tous les droits. Le mot de passe de l'utilisateur initial \textbf{root} est vide ou définit pendant l'installation.
\vspace{5mm} \\
\hspace{1mm}  Deux comptes \textbf{utilisateur anonyme} sont créés, qui peuvent faire ce qu'ils veulent avec toutes les tables dans la base de données '\textbf{test}' ou commençant par '\textbf{test\_}'. Cela signifie qu'un utilisateur peut se connecter sans mot de passe et être traité comme un utilisateur anonyme.
\vspace{5mm} \\
\hspace{1mm}  Les connexions doivent être faîtes en spécifiant le nom d'hôte. Ces comptes ont tous les droits dans les bases \textbf{test} ou dont le nom commence par \textbf{test\_}.
\vspace{5mm} \\
 
 
\hspace{1mm} Pour modifier les mots de passe utiliser \textbf{SET PASSWORD} \\
\hspace{1mm} \textbf{shell> mysql -u root} \\
\hspace{1mm} \textbf{mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('nouveau\_mot') ;} \\
\hspace{1mm} \textbf{mysql> SET PASSWORD FOR ''@'host\_name' = PASSWORD('nouveau\_mot') ;} \\
\hspace{1mm} Pour connaître le \textbf{host\_name} taper \\
\hspace{1mm} \textbf{mysql> SELECT Host, User FROM mysql.user ;} \\
\hspace{1mm} On peut utiliser la commande UPDATE pour changer les mots de passe des comptes anonymes. \\
\hspace{1mm} \textbf{shell> mysql -u root} \\
\hspace{1mm} \textbf{mysql> UPDATE mysql.user SET Password = PASSWORD('nouveau\_mot') WHERE User = '' ;} \\
\hspace{1mm} flush privileges demande au serveur de relire les tables de droits \\
\hspace{1mm} \textbf{mysql> FLUSH PRIVILEGES ;} \\
\hspace{1mm} si on préfère supprimer les comptes anonymes \\
\hspace{1mm} \textbf{shell> mysql -u root} \\
\hspace{1mm} \textbf{mysql> DELETE FROM mysql.user WHERE User = '' ;} \\
\hspace{1mm} \textbf{mysql> FLUSH PRIVILEGES ;} \\
 
\vspace{1cm}{\huge Introduction }
 
\
 
\hspace{1mm} Connexion au serveur MySql \\
\hspace{1mm} \textbf{mysql -h hote -u utilisateur -p} \\
\hspace{1mm} pour se déconnecter \\
\hspace{1mm} \textbf{QUIT ou EXIT} \\
\hspace{1mm} pour obtenir le numéro de version du serveur MySQL \\
\hspace{1mm} \textbf{SELECT VERSION(), CURRENT\_DATE ;} \\
\hspace{1mm} entrer plusieurs requêtes sur une seule ligne \\
\hspace{1mm} \textbf{SELECT VERSION() ; SELECT NOW() ;} \\
\hspace{1mm} entrer une commande sur plusieurs, ligne : la fin d'une requête se termine par ' ;' \\
\hspace{1mm} \textbf{mysql> SELECT} \\
\hspace{1mm} \textbf{-> USER()} \\
\hspace{1mm} \textbf{-> ,} \\
\hspace{1mm} \textbf{-> CURRENT\_DATE ;} \\
\hspace{1mm} Annuler une requête en cour de frappe, sur une seule ligne taper \\
\hspace{1mm} \textbf{\textbackslash{}c} \\
\hspace{1mm} afficher les bases de données existantes \\
\hspace{1mm} \textbf{SHOW DATABASES ;} \\
\hspace{1mm} accéder à une base \\
\hspace{1mm} \textbf{use test} \\
\hspace{1mm} créer une base de donnée \\
\hspace{1mm} \textbf{create database test ;} \\
\hspace{1mm} se connecter à mysql directement dans une base \\
\hspace{1mm} \textbf{mysql -u utilisateur -ppassword test} \\
\hspace{1mm} créer une table \\
\hspace{1mm} \textbf{CREATE TABLE animal (nom VARCHAR(20), maitre VARCHAR(20),} \\
\hspace{1mm} \textbf{-> espece VARCHAR(20), sexe CHAR(1), naissance DATE, mort DATE) ;} \\
\hspace{1mm} pour afficher la structure de la table \\
\hspace{1mm} \textbf{describe animal ;} \\
\hspace{1mm} créer un fichier pour remplir une table, chaque champ doit être séparé par une tabulation \\
\hspace{1mm} pour importer un fichier dans une table : \\
\hspace{1mm} \textbf{LOAD DATA LOCAL INFILE "animal.txt" INTO TABLE animal ;} \\
\hspace{1mm} ajouter des enregistrement un par un (utiliser NULL pour indiquer une valeur manquante) \\
\hspace{1mm} \textbf{mysql> INSERT INTO animal} \\
\hspace{1mm} \textbf{-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL) ;} \\
\hspace{1mm} récupérer des informations \\
\hspace{1mm} \textbf{SELECT quoi\_selectionner} \\
\hspace{1mm} \textbf{FROM quel\_table} \\
\hspace{1mm} \textbf{WHERE conditions\_a\_satisfaire} \\
\hspace{1mm} pour tout sélectionner \\
\hspace{1mm} \textbf{SELECT * from animal ;} \\
\hspace{1mm} corriger une erreur dans une table, il existe au moins 2 façons de le faire. la première consiste a vider la table, corriger le fichier et le recharger \\
\hspace{1mm} \textbf{mysql> SET AUTOCOMMIT=1 ; \# Utilisé pour une recréation rapide de la table} \\
\hspace{1mm} \textbf{mysql> DELETE FROM animal ;} \\
\hspace{1mm} \textbf{mysql> LOAD DATA LOCAL INFILE "animal.txt" INTO TABLE animal ;} \\
\hspace{1mm} ou corriger l'enregistrement avec update \\
\hspace{1mm} \textbf{mysql> UPDATE animal SET naissance = "1989-08-31" WHERE nom = "Bowser" ;} \\
\hspace{1mm} sélectionner des lignes particulières : \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom = "Bowser" ;} \\
\hspace{1mm} avec les booléennes \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE espece = "chien" AND sexe = "f" ;} \\
\hspace{1mm} plus complexe \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE (espece = "chat" AND sexe = "m")} \\
\hspace{1mm} \textbf{-> OR (espece = "chien" AND sexe = "f") ;} \\
\hspace{1mm} sélectionner des colonnes \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance FROM animal ;} \\
\hspace{1mm} pour minimiser le résultat à des champs uniques utiliser DISTINCT \\
\hspace{1mm} \textbf{mysql> SELECT DISTINCT maitre FROM animal ;} \\
\hspace{1mm} trie des enregistrements \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance FROM animal ORDER BY naissance ;} \\
\hspace{1mm} pour trier en respectant la casse \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance FROM animal ORDER BY BINARY naissance ;} \\
\hspace{1mm} pour trier dans l'ordre décroissant \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance FROM animal ORDER BY naissance DESC ;} \\
\hspace{1mm} on peut trier sur plusieurs colonnes \\
\hspace{1mm} \textbf{mysql> SELECT nom, espece, naissance FROM animal ORDER BY espece, naissance DESC ;} \\
\hspace{1mm} ici l'ordre décroissant n'est appliqué qu'à naissance. \\
 
\vspace{1cm}{\huge Calcul des Dates }
 
\
 
\hspace{1mm} Pour calculer l'âge par rapport à une date de naissance, il faut calculer la différence entre l'année en cour et l'année de naissance, puis soustraire la date courante si la date du jour se produit plus tôt dans l'année civile que la date de naissance \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance, CURRENT\_DATE,} \\
\hspace{1mm} \textbf{-> (YEAR(CURRENT\_DATE)-YEAR(naissance))} \\
\hspace{1mm} \textbf{-> - (RIGHT(CURRENT\_DATE,5)<RIGHT(naissance,5))} \\
\hspace{1mm} \textbf{-> AS age} \\
\hspace{1mm} \textbf{-> FROM animal ;} \\
\hspace{1mm} YEAR extrait l'année de la date et RIGHT(naissance,5) extrait les 5 caractères les plus à droite de la date qui représente MM-DD (année civile). \\
\hspace{1mm} Pour calculer l'âge d'un animal à sa mort, s'il est déjà mort \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance, mort,} \\
\hspace{1mm} \textbf{-> (YEAR(mort)-YEAR(naissance)) - (RIGHT(mort,5)<RIGHT(naissance,5))} \\
\hspace{1mm} \textbf{-> AS age} \\
\hspace{1mm} \textbf{-> FROM animal WHERE mort IS NOT NULL ORDER BY age ;} \\
\hspace{1mm} si l'animal est mort, le champ mort est différent de NULL. \\
\hspace{1mm} pour afficher dans combien de mois est leur anniversaire \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance, MONTH(naissance) FROM animal ;} \\
\hspace{1mm} ceux dont l'anniversaire est dans 5 mois : \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance FROM animal WHERE MONTH(naissance) = 5 ;} \\
\hspace{1mm} autres valeurs utilisables : YEAR(), MONTH(), et DAYOFMONTH(). \\
\hspace{1mm} on peut utiliser des fonctions de calcul d'intervalle \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance FROM animal} \\
\hspace{1mm} \textbf{-> WHERE MONTH(naissance) = MONTH(DATE\_ADD(NOW(), INTERVAL 1 MONTH)) ;} \\
\hspace{1mm} autre manière pour un résultat similaire \\
\hspace{1mm} \textbf{mysql> SELECT nom, naissance FROM animal} \\
\hspace{1mm} \textbf{-> WHERE MONTH(naissance) = MOD(MONTH(NOW()), 12) + 1 ;} \\
\hspace{1mm} valeur NULL : on ne peut pas utiliser de comparateur : '=', '<', '>' ou '<>'. il faut utiliser IS NULL ou IS NOT NULL. \\
 
\vspace{1cm}{\huge Recherche de modèles }
 
\
 
\hspace{1mm} '\_' représente n'importe quel caractère, '\%' un nombre arbitraire de caractères. on n'utilise pas = ni <> mais LIKE et NOT LIKE. \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom LIKE "b\%" ;} \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom LIKE "\%w\%" ;} \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom LIKE "\_\_\_\_\_" ;} \\
\hspace{1mm} les expressions régulières étendues. avec ce type de modèle, utiliser REGEXP et NOT REGEXP ou RLIKE et NOT RLIKE \\
\hspace{1mm} '.' représente n'importe quel caractère, [...] n'importe quel caractère inclus entre les crochets, fonctionne aussi comme [a-z]. \\
\hspace{1mm} '*' nombre arbitraire de caractère, '\textasciicircum ' pour spécifier en début de chaîne ou '\$' en fin de chaîne. REGEXP n'est pas sensible à la casse, utiliser binary pour la respecter. \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom REGEXP "\textasciicircum b" ;} \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom REGEXP "\textasciicircum [bB]" ;} \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom REGEXP BINARY "\textasciicircum b" ;} \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom REGEXP "fy\$" ;} \\
\hspace{1mm} REGEXP cherche le motif recherché donc pas besoin de * au contraire de LIKE : \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom REGEXP "w" ;} \\
\hspace{1mm} pour recherche une chaîne exacte : .n signifie répéter n fois. \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom REGEXP "\textasciicircum .....\$" ;} \\
\hspace{1mm} \textbf{mysql> SELECT * FROM animal WHERE nom REGEXP "\textasciicircum .5\$" ;} \\
 
\vspace{1cm}{\huge Compter les lignes }
 
\
 
\hspace{1mm} La fonction COUNT() compte le nombre de résultats non NULL \\
\hspace{1mm} \textbf{mysql> SELECT COUNT(*) FROM animal ;} \\
\hspace{1mm} \textbf{mysql> SELECT maitre, COUNT(*) FROM animal GROUP BY maitre ;} \\
\hspace{1mm} la clause GROUP BY est nécessaire pour grouper tous les enregistrements par maître, sinon on a une erreur \\
\hspace{1mm} \textbf{mysql> SELECT espece, sexe, COUNT(*) FROM animal GROUP BY espece, sexe ;} \\
\hspace{1mm} \textbf{mysql> SELECT espece, sexe, COUNT(*) FROM animal} \\
\hspace{1mm} \textbf{-> WHERE espece = "chien" OR espece = "chat"} \\
\hspace{1mm} \textbf{-> GROUP BY espece, sexe ;} \\
\hspace{1mm} \textbf{mysql> SELECT espece, sexe, COUNT(*) FROM animal} \\
\hspace{1mm} \textbf{-> WHERE sexe IS NOT NULL} \\
\hspace{1mm} \textbf{-> GROUP BY espece, sexe ;} \\
 
\vspace{1cm}{\huge Utiliser plus d'une table }
 
\
 
\hspace{1mm} \textbf{mysql> SELECT animal.nom,} \\
\hspace{1mm} \textbf{-> (TO\_DAYS(date) - TO\_DAYS(naissance))/365 AS age, remarque} \\
\hspace{1mm} \textbf{-> FROM animal, evenement} \\
\hspace{1mm} \textbf{-> WHERE animal.nom = evenement.nom AND type = "mise bas" ;} \\
\hspace{1mm} on peut joindre une table sur elle-même \\
\hspace{1mm} \textbf{mysql> SELECT p1.nom, p1.sexe, p2.nom, p2.sexe, p1.espece} \\
\hspace{1mm} \textbf{-> FROM animal AS p1, animal AS p2} \\
\hspace{1mm} \textbf{-> WHERE p1.espece = p2.espece AND p1.sexe = "f" AND p2.sexe = "m" ;} \\
\hspace{1mm} Obtenir des informations à propos des bases de données et des tables \\
\hspace{1mm} \textbf{Show databases} \\
\hspace{1mm} montrer dans quelle base on est \\
\hspace{1mm} \textbf{select database()} \\
\hspace{1mm} montrer les tables \\
\hspace{1mm} \textbf{Show Tables} \\
\hspace{1mm} afficher les champs de la table \\
\hspace{1mm} \textbf{Describe animal} \\
 
\vspace{1cm}{\huge Utilisation de mysql en mode batch }
 
\
 
\hspace{1mm} Pour utiliser mysql en mode batch, placer les commandes dans un fichier et utiliser la commande \\
\hspace{1mm} \textbf{mysql < fichier-batch} \\
\hspace{1mm} capturer l'affichage \\
\hspace{1mm} \textbf{mysql < fichier.batch > mysql.out} \\
\hspace{1mm} la sortie en mode batch est différente du mode interactif, pour obtenir le même affichage utiliser l'option mysql -t . Pour écrire les commandes \\
\hspace{1mm} \textbf{mysql -vvv.} \\
\hspace{1mm} pour exécuter un fichier depuis la commande \\
\hspace{1mm} \textbf{source nom\_fichier} \\
 
\vspace{1cm}{\huge Exemples de requêtes usuelles }
 
\
 
\hspace{1mm} valeur maximale d'une colonne \\
\hspace{1mm} \textbf{SELECT MAX(article) AS article FROM shop} \\
\hspace{1mm} la ligne contenant le maximum d'une certaine colonne \\
\hspace{1mm} \textbf{SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop) ;} \\
\hspace{1mm} la même chose en triant dans l'ordre décroissant, puis afficher la première ligne \\
\hspace{1mm} \textbf{SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1 ;} \\
\hspace{1mm} maximum d'une colonne par groupe \\
\hspace{1mm} \textbf{SELECT article, MAX(price) AS price FROM shop GROUP BY article} \\
\hspace{1mm} la ligne contenant la plus grande valeur d'un certain champ par rapport à un groupe \\
\hspace{1mm} \textbf{SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) ;} \\
\hspace{1mm} la même en plusieurs étapes, en créant une table temporaire \\
\hspace{1mm} \textbf{CREATE TEMPORARY TABLE tmp (} \\
\hspace{1mm} \textbf{article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,} \\
\hspace{1mm} \textbf{price DOUBLE(16,2) DEFAULT '0.00' NOT NULL) ;} \\
\hspace{1mm} \textbf{LOCK TABLES shop read ;} \\
\hspace{1mm} \textbf{INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article ;} \\
\hspace{1mm} \textbf{SELECT shop.article, dealer, shop.price FROM shop, tmp} \\
\hspace{1mm} \textbf{WHERE shop.article=tmp.article AND shop.price=tmp.price ;} \\
\hspace{1mm} \textbf{UNLOCK TABLES ;} \\
\hspace{1mm} \textbf{DROP TABLE tmp ;} \\
\hspace{1mm} ou encore (en une étape) \\
\hspace{1mm} \textbf{SELECT article,} \\
\hspace{1mm} \textbf{SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,} \\
\hspace{1mm} \textbf{0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price} \\
\hspace{1mm} \textbf{FROM shop} \\
\hspace{1mm} \textbf{GROUP BY article ;} \\
\hspace{1mm} Utiliser les variables utilisateur. Trouver l'article avec le plus haut et le plus bas prix \\
\hspace{1mm} \textbf{mysql> SELECT @min\_price :=MIN(price),@max\_price :=MAX(price) FROM shop ;} \\
\hspace{1mm} \textbf{mysql> SELECT * FROM shop WHERE price=@min\_price OR price=@max\_price ;} \\
\hspace{1mm} utiliser les clés étrangères \\
\hspace{1mm} \textbf{CREATE TABLE person (} \\
\hspace{1mm} \textbf{id SMALLINT UNSIGNED NOT NULL AUTO\_INCREMENT,} \\
\hspace{1mm} \textbf{name CHAR(60) NOT NULL,} \\
\hspace{1mm} \textbf{PRIMARY KEY (id)} \\
\hspace{1mm} \textbf{) ;} \\
\hspace{1mm} \textbf{CREATE TABLE shirt (} \\
\hspace{1mm} \textbf{id SMALLINT UNSIGNED NOT NULL AUTO\_INCREMENT,} \\
\hspace{1mm} \textbf{style ENUM('t-shirt', 'polo', 'dress') NOT NULL,} \\
\hspace{1mm} \textbf{color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,} \\
\hspace{1mm} \textbf{owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),} \\
\hspace{1mm} \textbf{PRIMARY KEY (id)} \\
\hspace{1mm} \textbf{) ;} \\
\hspace{1mm} \textbf{INSERT INTO person VALUES (NULL, 'Antonio Paz') ;} \\
\hspace{1mm} \textbf{INSERT INTO shirt VALUES} \\
\hspace{1mm} \textbf{(NULL, 'polo', 'blue', LAST\_INSERT\_ID()),} \\
\hspace{1mm} \textbf{(NULL, 'dress', 'white', LAST\_INSERT\_ID()),} \\
\hspace{1mm} \textbf{(NULL, 't-shirt', 'blue', LAST\_INSERT\_ID()) ;} \\
\hspace{1mm} \textbf{INSERT INTO person VALUES (NULL, 'Lilliana Angelovska') ;} \\
\hspace{1mm} \textbf{INSERT INTO shirt VALUES} \\
\hspace{1mm} \textbf{(NULL, 'dress', 'orange', LAST\_INSERT\_ID()),} \\
\hspace{1mm} \textbf{(NULL, 'polo', 'red', LAST\_INSERT\_ID()),} \\
\hspace{1mm} \textbf{(NULL, 'dress', 'blue', LAST\_INSERT\_ID()),} \\
\hspace{1mm} \textbf{(NULL, 't-shirt', 'white', LAST\_INSERT\_ID()) ;} \\
 
\hspace{1mm} SELECT * FROM person ;
 
{\ttfamily
+----+---------------------+\\ |-id-|---------name--------|\\ +----+---------------------+\\ |--1-|-Antonio-Paz---------|\\ |--2-|-Lilliana-Angelovska-|\\ +----+---------------------+\\
}
\vspace{8mm}
 
\hspace{1mm} SELECT * FROM shirt ;
 
{\ttfamily
+----+---------+--------+-------+\\ |-id-|--style--|-color--|-owner-|\\ +----+---------+--------+-------+\\ |--1-|-polo----|-blue---|----1--|\\ |--2-|-dress---|-white--|----1--|\\ |--3-|-t-shirt-|-blue---|----1--|\\ |--4-|-dress---|-orange-|----2--|\\ |--5-|-polo----|-red----|----2--|\\ |--6-|-dress---|-blue---|----2--|\\ |--7-|-t-shirt-|-white--|----2--|\\ +----+---------+--------+-------+\\
}
\vspace{8mm}
 
\hspace{1mm} SELECT s.* FROM person p, shirt s WHERE p.name LIKE 'Lilliana\%' AND s.owner = p.id AND s.color <> 'white' ;
 
{\ttfamily
+----+-------+--------+-------+\\ |-id-|-style-|-color--|-owner-|\\ +----+-------+--------+-------+\\ |--4-|-dress-|-orange-|-2-----|\\ |--5-|-polo--|--red---|-2-----|\\ |--6-|-dress-|-blue---|-2-----|\\ +----+-------+--------+-------+\\
}
\vspace{8mm}
 
\hspace{1mm} Recherche sur deux clefs \\
\hspace{1mm} \textbf{SELECT champ1\_index, champ2\_index FROM test\_table WHERE champ1\_index = '1'} \\
\hspace{1mm} \textbf{OR champ2\_index = '1'} \\
\hspace{1mm} avec UNION \\
\hspace{1mm} \textbf{SELECT field1\_index, field2\_index FROM test\_table WHERE field1\_index = '1'} \\
\hspace{1mm} \textbf{UNION} \\
\hspace{1mm} \textbf{SELECT field1\_index, field2\_index FROM test\_table WHERE field2\_index = '1' ;} \\
\hspace{1mm} calcul du nombre de visites par jour \\
\hspace{1mm} \textbf{CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,} \\
\hspace{1mm} \textbf{day INT(2) UNSIGNED ZEROFILL) ;} \\
\hspace{1mm} \textbf{INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),} \\
\hspace{1mm} \textbf{(2000,2,23),(2000,2,23) ;} \\
\hspace{1mm} \textbf{SELECT year,month,BIT\_COUNT(BIT\_OR(1<<day)) AS days FROM t1} \\
\hspace{1mm} \textbf{GROUP BY year,month ;} \\
\hspace{1mm} Utiliser AUTO\_INCREMENT. L'attribut AUTO\_INCREMENT peut être utilisé pour générer un identifiant unique pour les nouvelles lignes \\
\hspace{1mm} \textbf{CREATE TABLE animals (} \\
\hspace{1mm} \textbf{id MEDIUMINT NOT NULL AUTO\_INCREMENT,} \\
\hspace{1mm} \textbf{name CHAR(30) NOT NULL,} \\
\hspace{1mm} \textbf{PRIMARY KEY (id)} \\
\hspace{1mm} \textbf{) ;} \\
\hspace{1mm} \textbf{INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),} \\
\hspace{1mm} \textbf{("lax"),("whale"),("ostrich") ;} \\
\hspace{1mm} \textbf{SELECT * FROM animals ;} \\
\hspace{1mm} On peut récupérer la valeur utilisée de la clé AUTO\_INCREMENT avec la fonction LAST\_INSERT\_ID() ou la fonction API mysql\_insert\_id(). \\
\hspace{1mm} Pour les tables MyISAM et BDB on peut spécifier AUTO\_INCREMENT sur une colonne secondaire d'une clef multi-colonnes. Dans ce cas la valeur généré est calculée de la façon suivante : \\
\hspace{1mm} \textbf{MAX(auto\_increment\_column)+1) WHERE prefix=given-prefix .} \\
\hspace{1mm} Utile lorsqu'on veut placer des données dans des groupes ordonnés. \\
\hspace{1mm} \textbf{CREATE TABLE animals (} \\
\hspace{1mm} \textbf{grp ENUM('fish','mammal','bird') NOT NULL,} \\
\hspace{1mm} \textbf{id MEDIUMINT NOT NULL AUTO\_INCREMENT,} \\
\hspace{1mm} \textbf{name CHAR(30) NOT NULL,} \\
\hspace{1mm} \textbf{PRIMARY KEY (grp,id)} \\
\hspace{1mm} \textbf{) ;} \\
\hspace{1mm} \textbf{INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),} \\
\hspace{1mm} \textbf{("bird","penguin"),("fish","lax"),("mammal","whale"),} \\
\hspace{1mm} \textbf{("bird","ostrich") ;} \\
\hspace{1mm} \textbf{SELECT * FROM animals ORDER BY grp,id ;} \\
 
\hspace{1mm} Qui retourne :
 
{\ttfamily
+--------+----+---------+\\ |--grp---|-id-|-name----|\\ +--------+----+---------+\\ |-fish---|--1-|-lax-----|\\ |-mammal-|--1-|-dog-----|\\ |-mammal-|--2-|-cat-----|\\ |-mammal-|--3-|-whale---|\\ |-bird---|--1-|-penguin-|\\ |-bird---|--2-|-ostrich-|\\ +--------+----+---------+
}
\vspace{8mm}
 
\vspace{1cm}{\huge Utilisation de MySQL avec Apache }
 
\
 
\hspace{1mm} Pour changer le format d'archivage d'Apache dans MySQL pour le rendre plus lisible, mettre ceci dans la configuration d'Apache : \\
\hspace{1mm} \textbf{LogFormat \textbackslash{}} \\
\hspace{1mm} \textbf{"\textbackslash{}"\%h\textbackslash{}",\%\%Y\%m\%d\%H\%M\%St,\%>s,\textbackslash{}"\%b\textbackslash{}",\textbackslash{}"\%Content-Typeo\textbackslash{}", \textbackslash{}} \\
\hspace{1mm} \textbf{\textbackslash{}"\%U\textbackslash{}",\textbackslash{}"\%Refereri\textbackslash{}",\textbackslash{}"\%User-Agenti\textbackslash{}""} \\
\hspace{1mm} Avec MySQL, vous pouvez exécuter une requête de cette manière : \\
\hspace{1mm} \textbf{LOAD DATA INFILE '/local/access\_log' INTO TABLE table\_name} \\
\hspace{1mm} \textbf{FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\textbackslash{}\textbackslash{}'} \\
 
\vspace{1cm}{\huge Administration du serveur }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} mysqld]{est le serveur MySQL}
\item[\hspace{1mm} mysqld-max]{version du serveur qui inclut des fonctionnalités supplémentaires}
\item[\hspace{1mm} mysqld-safe]{est un script de démarrage du serveur. Tente de démarrer mysqld-max s'il existe sinon mysqld.}
\item[\hspace{1mm} mysql.server]{est un script de démarrage du serveur, utilisé sur les systèmes qui ont un dossier contenant des services système. Il invoque mysqld-safe pour démarrer le serveur.}
\item[\hspace{1mm} mysqld\_multi]{est un script de démarrage qui peut lancer ou arrêter différentes instances du serveur, installées sur le système.}
\item[\hspace{1mm} mysql\_install\_db]{crée les tables de droits MySQL, avec les droits par défaut.}
\item[\hspace{1mm} mysql\_fix\_privilege\_tables]{script utilisé après une mise à jour de MySQL pour mettre à jour les tables de droits, et les adapter aux nouvelles versions de MySQL.}
\item[\hspace{1mm} myisamchk]{utilitaire pour décrire, vérifier, optimiser et réparer les tables MyISAM}
\item[\hspace{1mm} make\_binary\_distribution]{crée une version compilée de MySQL.}
\item[\hspace{1mm} mysqlbug]{script de rapport de bug de MySQL.}
\end{description}
 
\hspace{1mm}  Pour connaître les moteurs de stockage que votre serveur supporte, utiliser la commande \textbf{SHOW ENGINES;} \textbf{safe\_mysqld} est la méthode recommandée pour démarrer un démon mysqld. Il ajoute des fonctionnalités de sécurité telles que le redémarrage automatique lorsqu'une erreur survient et l'enregistrement d'informations d'exécution dans un fichier de log. Par défaut il essaie de lancer mysqld-max s'il existe. Pour remplacer le comportement par défaut et spécifier explicitement le serveur à utiliser, spécifier \textbf{--mysqld} ou \textbf{--mysqld-version}.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Le mode SQL du serveur }
 
\
 
\hspace{1mm}  Le serveur MySQL peut fonctionner avec différents modes SQL, et peut s'appliquer au niveau de la connexion du client. Cela permet aux applications d'adapter le comportement du serveur en fonction de leur attentes. Le mode définit quelle syntaxe SQL le serveur doit supporter, et quels types de vérification il doit faire. Pour donner un mode par défaut au démarrage : \textbf{--sql-mode="modes"}. On peut modifier le mode à chaud avec \textbf{SET [SESSION|GLOBAL] sql\_mode='modes'}
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Modes }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} ANSI]{change la syntaxe et le comportement pour être plus compatible avec le standard SQL}
\item[\hspace{1mm} STRICT\_TRANS\_TABLES]{Si une valeur n'a pu être insérée dans une table transactionnelle sans modification, la commande est annulée. Pour une tables non-transactionnelle, la commande est annulée si cela survient dans une ligne unique ou dans la première ligne d'une insertion multiple.}
\item[\hspace{1mm} TRADITIONAL]{le serveur se comporte comme un système SQL traditionnel}
\item[\hspace{1mm} ALLOW\_INVALID\_DATES]{N'autorise pas la vérification totale des dates. Vérifie simplement que le mois est dans l'intervalle de 1 à 12, et que le jour est dans l'intervalle de 1 à 31. C'est très pratique pour les applications Web où la date est obtenue de 3 champs différents, et que vous voulez stocker exactement la date saisie sans validation. Ce mode s'applique aux colonnes de type DATE et DATETIME. Il ne s'applique pas aux colonnes TIMESTAMP, qui demandent toujours une date valide.}
\item[\hspace{1mm} ANSI\_QUOTES]{Traite ‘"’ comme un délimiteur d'identifiant (comme le caractère MySQL ‘`’) et non comme un délimiteur de chaînes. Vous pouvez toujours utiliser ‘`’ pour délimiter les identifiants en mode ANSI. Avec ANSI\_QUOTES activée, vous ne pouvez pas utiliser les guillemets doubles pour délimiter une chaîne de caractères, car ce sera uniquement interprété comme un identifiant.}
\item[\hspace{1mm} ERROR\_FOR\_DIVISION\_BY\_ZERO]{Produit une erreur en mode strict et sinon une alerte, lorsque MySQL doit tenter une division par 0 ou un MOD(X,0)) durant une commande INSERT/ UPDATE. Si ce mode n'est pas activé, MySQL retourne simplement NULL pour les divisions par zéro. Si utilisé avec l'attribut IGNORE, MySQL génère une alerte pour les divisions par zéro, mais le résultat de l'opération sera NULL}
\item[\hspace{1mm} IGNORE\_SPACE]{Permet les espaces entre le nom de la fonction et le caractère ‘(’. Cela force les noms de fonctions a être traités comme des mots réservés. En conséquence, si vous voulez accéder aux bases, tables et colonnes dont le nom est un mot réservé, vous devez le mettre entre délimiteurs.}
\item[\hspace{1mm} NO\_AUTO\_VALUE\_ON\_ZERO]{affecte la gestion des colonnes de type AUTO\_INCREMENT. Normalement, vous générez le prochain numéro de séquence dans la colonne en insérant soit NULL soit 0 dedans. NO\_AUTO\_VALUE\_ON\_ZERO supprime ce comportement pour 0 pour que seule la valeur NULL génère le prochain numéro de séquence. Ce mode est utile si vous avez stocké la valeur 0 dans la colonne AUTO\_INCREMENT de la table. Ce n'est pas recommandé. Par exemple, si vous voulez exporter une table avec mysqldump et que vous la rechargez, normalement MySQL va générer de nouveaux identifiants pour les lignes avec la valeur 0, ce qui entraînera une différence avec la table originale. En activant NO\_AUTO\_VALUE\_ON\_ZERO avant de recharger le fichier exporter, vous évitez des problèmes. mysqldump va automatiquement inclure les commandes nécessaires dans l'export, pour activer NO\_AUTO\_VALUE\_ON\_ZERO.}
\item[\hspace{1mm} NO\_DIR\_IN\_CREATE]{Lors de la création d'une table, ignore les directives INDEX DIRECTORY et DATA DIRECTORY. Cette option est pratique sur un esclave de réplication.}
\item[\hspace{1mm} NO\_FIELD\_OPTIONS]{N'affiche pas les options spécifiques à MySQL dans le résultat de SHOW CREATE TABLE. Ce mode est utilisé par mysqldump dans un souci de portabilité}
\item[\hspace{1mm} NO\_KEY\_OPTIONS]{N'affiche pas les options spécifiques à MySQL dans le résultat de SHOW CREATE TABLE. Ce mode est utilisé par mysqldump dans un souci de portabilité.}
\item[\hspace{1mm} NO\_TABLE\_OPTIONS]{N'affiche pas les options de tables spécifiques à MySQL (comme ENGINE) dans le résultat de SHOW CREATE TABLE. Ce mode est utilisé par mysqldump dans un souci de portabilité.}
\item[\hspace{1mm} NO\_ZERO\_DATE]{Ne permet pas l'utilisation de '0000-00-00' comme date valide. Vous pouvez toujours insérer des dates nulles avec l'option IGNORE. NO\_ZERO\_IN\_DATE N'accepte pas les dates où le mois ou le jour vaut 0. Si utilisé avec L'option IGNORE, la date '0000-00-00' sera insérée pour chaque date invalide.}
\item[\hspace{1mm} NO\_UNSIGNED\_SUBTRACTION]{Dans les opérations de soustraction, ne marque pas le résultat UNSIGNED si un des opérandes est non signé. Notez que cela fait que UNSIGNED BIGINT n'est plus totalement utilisable dans tous les contextes.}
\item[\hspace{1mm} ONLY\_FULL\_GROUP\_BY]{N'autorise pas les requêtes dont la clause GROUP BY fait référence à une colonne qui n'est pas sélectionnée.}
\item[\hspace{1mm} PIPES\_AS\_CONCAT]{Traite || comme un opérateur de concaténation (identique à CONCAT()) au lieu d'être un synonyme de OR.}
\item[\hspace{1mm} REAL\_AS\_FLOAT]{Traite le type REAL comme un synonyme FLOAT plutôt que comme un synonyme de DOUBLE.}
\item[\hspace{1mm} STRICT\_ALL\_TABLES]{Active le mode strict pour tous les moteurs de stockage. Les valeurs invalides sont rejetées. Plus de détails suivent.}
\item[\hspace{1mm} STRICT\_TRANS\_TABLES]{Active le mode strict pour tous les moteurs de stockage transactionnels. Les valeurs invalides sont rejetées. Plus de détails suivent.}
\end{description}
 
\vspace{1cm}{\huge Variables système dynamique }
 
\
 
\hspace{1mm} modifier une variable au lancement du serveur \\
\hspace{1mm} \textbf{mysqld --key\_buffer\_size=16M} \\
\hspace{1mm} modifier une variable à chaud \\
\hspace{1mm} \textbf{mysql> SET sort\_buffer\_size = 10 * 1024 * 1024 ;} \\
\hspace{1mm} Spécifier explicitement s'il s'agit d'une variable global ou de session \\
\hspace{1mm} \textbf{mysql> SET GLOBAL sort\_buffer\_size = 10 * 1024 * 1024 ;} \\
\hspace{1mm} \textbf{mysql> SET SESSION sort\_buffer\_size = 10 * 1024 * 1024 ;} \\
\hspace{1mm} Voir les variables du serveur \\
\hspace{1mm} \textbf{SHOW variables} \\
\hspace{1mm} Affiche des informations sur le statut du serveur \\
\hspace{1mm} \textbf{SHOW STATUS} \\
\hspace{1mm} Remet à 0 de nombreuses variables de status \\
\hspace{1mm} \textbf{FLUSH STATUS} \\
\hspace{1mm} Arrêter le serveur: si un utilisateur a les droit SHUTDOWN il peut arrêter le serveur avec \\
\hspace{1mm} \textbf{mysqladmin shutdown} \\
 
\vspace{1cm}{\huge Sécurité général du serveur }
 
\
 
\hspace{1mm}  MySQL dispose d'un système de sécurité basé sur des liste de contrôle d'accès (ACL) pour toutes les connexions, requêtes et opérations que l'utilisateur peut faire. Il y'a aussi le support des connexions SSL entre le client et le serveur MySQL.
\vspace{5mm} \\
\hspace{1mm}  \textbf{Suivre les règles suivante:}
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Ne donnez jamais à personne (sauf au compte MySQL root ) accès à la table user de la base mysql !. Le mot de passe chiffré est le vrai mot de passe de MySQL.}
\item[\hspace{1mm} -]{Apprenez le système de droits MySQL. Les commandes GRANT et REVOKE sont utilisés pour contrôler les accès à MySQL. Ne donnez pas plus de droits nécessaire. Ne donnez jamais de droits à tous les serveurs hôtes.}
\item[\hspace{1mm} -]{Ne stockez jamais de mot de passe en clair dans votre base. À la place, utilisez MD5(), SHA1() ou une autre fonction de signature injective}
\item[\hspace{1mm} -]{utilisez des mots de passe fort.}
\item[\hspace{1mm} -]{Placez votre serveur derrière un parefeu}
\end{description}
 
\vspace{1cm}{\huge Liste de vérification }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Essayez la commande \textbf{mysql -u root} sans mot de passe}
\item[\hspace{1mm} -]{Utilisez la commande \textbf{SHOW GRANTS} et vérifiez qui a accès à quoi. Puis utilisez la commande \textbf{REVOKE} pour retirer les droits inutiles.}
\end{description}
 
\vspace{1cm}{\huge Protéger MySQL contre les attaques }
 
\
 
\hspace{1mm}  Toutes les informations autre que les mots de passes sont transférées en clair. Pour sécuriser le traffic, il est recommandé d'utiliser le protocole compressé et d'utiliser ssh pour établir des connexions chiffrées.
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{N'exécutez jamais le démon MySQL avec l'utilisateur root. Pour démarrer mysqld sous un autre nom d'utilisateur Unix, ajoutez la ligne user qui spécifie le nom de l'utilisateur, dans le fichier d'options de [mysqld] de /etc/my.cnf.}
\item[\hspace{1mm} -]{N'autorisez pas l'utilisation de liens symboliques pour les tables. Cette fonctionnalité peut être désactivée avec l'option \textbf{--skip-symbolic-links}. C'est particulièrement important si vous utilisez mysqld comme root, car tout utilisateur a alors le droit d'écrire des données sur le disque, n'importe où sur le système !!}
\item[\hspace{1mm} -]{Vérifiez que l'utilisateur Unix qui exécute mysqld est le seul utilisateur avec les droits de lecture et écriture dans le dossier de base de données.}
\item[\hspace{1mm} -]{Ne donnez pas le droit de PROCESS à tous les utilisateurs. Le droit SUPER peut être utilisé pour fermer des connexions clients, changer les variables systèmes et contrôler la réplication.}
\item[\hspace{1mm} -]{Ne donnez pas le droit de FILE à tous les utilisateurs. Tout utilisateur qui possède ce droit peut écrire un fichier n'importe où sur le serveur, avec les droits hérités du démon mysqld.}
\item[\hspace{1mm} -]{Si vous voulez restreindre le nombre de connexions d'un utilisateur, vous pouvez le faire en utilisant la variable \textbf{max\_user\_connections} de mysqld. La commande GRANT dispose aussi d'options de contrôle des ressources, pour limiter l'utilisation du serveur par un compte utilisateur.}
\end{description}
 
\vspace{1cm}{\huge Options de démarrage qui concerne la sécurité }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} --local-infile\texttt{[}=(0|1)\texttt{]}]{si vous utilisez --local-infile=0 alors vous ne pourrez pas utiliser LOAD DATA LOCAL INFILE}
\item[\hspace{1mm} --safe-user=create]{activé, tout utilisateur ne peut pas créer d'autres utilisateurs avec les droits de GRANT, s'il ne dispose pas des droits d'insertion dans la table mysql.user Si vous voulez donner un accès à un utilisateur pour qu'il puisse créer des utilisateurs avec les droits dont il dispose, vous pouvez lui donner les droits suivant : \textbf{GRANT INSERT(user) ON mysql.user TO 'user'@'hostname';} cela va assurer que l'utilisateur ne peut pas modifier une colonne directement, mais qu'il peut exécuter la commande GRANT sur d'autres utilisateurs.}
\item[\hspace{1mm} --secure-auth]{interdit l'identification pour les comptes qui ont d'anciens mot de passe (<4.1.1)}
\item[\hspace{1mm} --skip-grant-tables]{force le serveur à ne pas utiliser les tables de droits. Donne tous les droits a tous les utilisateurs.}
\item[\hspace{1mm} --skip-name-resolve]{les nom d'hôtes ne sont pas résolus. toutes les valeurs de la colonne host dans les tables de droits doivent être des adresses ip, ou bien localhost}
\item[\hspace{1mm} --skip-networking]{Ne pas accepter les connexions TCP/IP venant du réseau, mais uniquement via socket Unix.}
\end{description}
 
\vspace{1cm}{\huge Problèmes de sécurité avec LOAD DATA LOCAL }
 
\
 
\hspace{1mm}  La commande \textbf{LOAD DATA} peut lire des données sur le serveur hôte ou charger un fichier sur le client avec LOCAL.
\vspace{5mm} \\
\hspace{1mm}  Pour le client en ligne de commande mysql, \textbf{LOAD DATA LOCAL} peut être activé en spécifiant l'option \textbf{--local-infile[=1]}, ou désactivé avec \textbf{--local-infile=0}.
\vspace{5mm} \\
\hspace{1mm}  Vous pouvez désactiver toutes les commandes \textbf{LOAD DATA LOCAL} du serveur MySQL en démarrant mysqld avec \textbf{--local-infile=0}. Similairement, pour mysqlimport, les options --local et -L activent le chargement distant de fichiers. Dans ce cas, il faut que le serveur accepte aussi cette configuration pour que l'opération fonctionne.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Règles de sécurité et droits d'accès au serveur }
 
\
 
{\includegraphics[]{../images/mysql-table1.png}}
 
\hspace{1mm}  Lors de la seconde étape du contrôle d'accès (vérification de la requête), le serveur peut, suivant la requête, consulter aussi les tables tables\_priv et columns\_priv. Les champs de ces tables sont:
\vspace{5mm} \\
 
 
{\includegraphics[]{../images/mysql-table2.png}}
 
\hspace{1mm}  Chaque table de droit contient des champs d'identification et des champs de droits.
\vspace{5mm} \\
\hspace{1mm}  - Les champs d'identification déterminent quels utilisateurs correspondent à cette ligne dans la table.
\vspace{5mm} \\
\hspace{1mm}  - Les champs de droits indiquent si le droit est donné. Les champs d'identification sont des chaînes:
\vspace{5mm} \\
 
 
{\includegraphics[]{../images/mysql-table3.png}}
 
\hspace{1mm}  Dans les tables \textbf{user}, \textbf{db} et \textbf{host} tous les champs de droits sont déclarés avec le type \textbf{ENUM('N','Y')}
\vspace{5mm} \\
\hspace{1mm}  Dans les tables \textbf{tables\_priv} et \textbf{column\_priv} les champs de droits sont déclarés comme des champs de type \textbf{SET}.
\vspace{5mm} \\
 
 
{\includegraphics[]{../images/mysql-table4.png}}
 
\hspace{1mm}  Le serveur utilise les tables de droits comme ceci :
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{La table user détermine si le serveur accepte ou rejette la connexion. Pour les connexions acceptées, tous les privilèges donnés dans la table user indiquent des privilèges globaux. Ces droits s'appliquent à toutes les bases du serveur.}
\item[\hspace{1mm} -]{Les champs d'identification de la table db déterminent quels utilisateurs peuvent accéder à quelles bases, depuis quel hôte. Les champs de droits indiquent les opérations permises. Les droits s'appliquent alors à toutes les bases du serveur.}
\item[\hspace{1mm} -]{La table host est utilisée comme extension de la table db lorsque vous voulez qu'une ligne de la table db s'applique à plusieurs hôtes dans votre réseau, laissez la colonne host vide dans la table db.}
\item[\hspace{1mm} -]{Les tables tables\_priv et columns\_priv sont similaires à la table db, mais elles s'appliquent au niveau des tables et des colonnes, plutôt qu'au niveau des tables.}
\end{description}
 
\hspace{1mm}  Notez que les droits d'administration ne sont spécifiés que dans la table user, tout comme FILE.
\vspace{5mm} \\
\hspace{1mm}  Pour voir les droit d'un utilisateur:
\vspace{5mm} \\
\hspace{1mm}  \textbf{SHOW GRANTS FOR 'bob'@'pc84.example.com';}
\vspace{5mm} \\
\hspace{1mm}  Un outil de diagnostique pratique est le script mysqlaccess.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Droits fournis par MySQL }
 
\
 
{\includegraphics[]{../images/mysql-table5.png}}
 
\hspace{1mm}  Les droits de \textbf{SELECT}, \textbf{INSERT}, \textbf{UPDATE} et \textbf{DELETE} vous permettent de faire des opérations sur les lignes qui existent, dans une table existante d'une base.
\vspace{5mm} \\
\hspace{1mm}  La commande \textbf{SELECT} requiert le droit de \textbf{SELECT} uniquement si des lignes sont lues dans une table. Vous pouvez exécuter une commande \textbf{SELECT} même sans aucun droit d'accès à une base de données dans le serveur. Par exemple, vous pourriez utiliser le client mysql comme une simple calculatrice :
\vspace{5mm} \\
\hspace{1mm}  mysql> SELECT 1+1;
\vspace{5mm} \\
\hspace{1mm}  mysql> SELECT PI()*2;
\vspace{5mm} \\
\hspace{1mm}  Le droit de \textbf{INDEX} vous donne le droit de créer et détruire des index de table.
\vspace{5mm} \\
\hspace{1mm}  Le droit de \textbf{ALTER} vous donne le droit de modifier une table avec la commande \textbf{ALTER TABLE}.Les droits de \textbf{CREATE} et \textbf{DROP} vous permettent de créer de nouvelles tables et bases de données, et de les supprimer.
\vspace{5mm} \\
\hspace{1mm}  Le droit de \textbf{GRANT} vous permet de donner les droits que vous possédez à d'autres utilisateurs.
\vspace{5mm} \\
 
 
\hspace{1mm}  Le droit de \textbf{FILE} vous donne la possibilité de lire et écrire des fichiers sur le serveur avec les commandes \textbf{LOAD DATA INFILE} et \textbf{SELECT ... INTO OUTFILE}.
\vspace{5mm} \\
\hspace{1mm}  Les autres droits sont utilisés pour les opérations administratives qui sont exécutées par l'utilitaire \textbf{mysqladmin}. La table ci-dessous montre quelle commande est associée à mysqladmin avec un de ces droits :
\vspace{5mm} \\
 
 
{\includegraphics[]{../images/mysql-table6.png}}
 
\hspace{1mm}  La commande \textbf{reload} indique au serveur de relire les tables de droits.
\vspace{5mm} \\
\hspace{1mm}  La commande \textbf{refresh} vide les tables de la mémoire, écrit les données et ferme le fichier de log. \textbf{flush-privileges} est un synonyme de \textbf{reload}. Les autres commandes \textbf{flush-*} effectuent des fonctions similaires à la commande \textbf{refresh} mais sont plus limitées dans leur application, et sont préférables dans certains contextes.
\vspace{5mm} \\
\hspace{1mm}  Par exemple, si vous souhaitez simplement vider les tampons dans le fichier de log, utilisez \textbf{flush-logs}, qui est un meilleur choix que \textbf{refresh}.
\vspace{5mm} \\
\hspace{1mm}  La commande \textbf{shutdown} éteint le serveur.
\vspace{5mm} \\
 
 
\hspace{1mm}  La commande \textbf{processlist} affiche les informations sur les threads qui s'exécutent sur le serveur. La commande \textbf{kill} termine un des threads du serveur. Vous pouvez toujours afficher et terminer vos propres threads, mais vous aurez besoin des droits de \textbf{PROCESS} pour afficher les threads, et le droit de SUPER pour terminer ceux qui ont été démarrés par d'autres utilisateurs.
\vspace{5mm} \\
\hspace{1mm}  C'est une bonne idée en général, de ne donner les droits de Grant qu'aux utilisateurs qui en ont besoin, et vous devriez être particulièrement vigilant pour donner certains droits :
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Le droit de \textbf{GRANT} permet aux utilisateurs de donner leurs droits à d'autres utilisateurs. Deux utilisateurs avec des droits différents et celui de \textbf{GRANT} pourront combiner leurs droits respectifs pour gagner un autre niveau d'utilisation du serveur.}
\item[\hspace{1mm} -]{Le droit de \textbf{ALTER} peut être utilisé pour tromper le système en renommant les tables.}
\item[\hspace{1mm} -]{Le droit de \textbf{FILE} peut servir à lire des fichiers accessibles à tous sur le serveur, et les placer dans une base de données. Le contenu pourra alors être lu et manipulé avec \textbf{SELECT}. Cela inclus le contenu de toutes les bases actuellement hébergées sur le serveur !}
\item[\hspace{1mm} -]{Le droit de \textbf{SHUTDOWN} peut conduire au dénis de service, en arrêtant le serveur.}
\item[\hspace{1mm} -]{Le droit de \textbf{PROCESS} permet de voir en texte clair les commandes qui s'exécutent actuellement, et notamment les changements de mot de passe.}
\item[\hspace{1mm} -]{Le droit de \textbf{SUPER} peut être utilisé pour terminer les connexions ou modifier le mode opératoire du serveur.}
\item[\hspace{1mm} -]{Les droits sur la base de données mysql peuvent être utilisés pour changer des mots de passe ou des droits dans la table des droits (Les mots de passe sont stockés chiffrés, ce qui évite que les intrus ne les lisent). S'ils accèdent à un mot de passe dans la table \textbf{mysql.user}, ils pourront l'utiliser pour se connecter au serveur avec cet utilisateur (avec des droits suffisants, le même utilisateur pourra alors remplacer un mot de passe par un autre).}
\end{description}
 
\vspace{1cm}{\huge Connexion au serveur MySQL }
 
\
 
\hspace{1mm} \textbf{shell> mysql [-h nom\_d\_hote] [-u nom\_d\_utilisateur] [-pvotre\_mot\_de\_passe]} \\
\hspace{1mm} \textbf{shell> mysql [--host=nom\_d\_hote] [--user=nom\_d\_utilisateur] [--password=votre\_mot\_de\_passe]} \\
\hspace{1mm} On peut spécifier les paramètres de connexion dans la section [client] du fichier de configuration my.cnf : \\
\hspace{1mm} \textbf{[client]} \\
\hspace{1mm} \textbf{host=nom\_d\_hote} \\
\hspace{1mm} \textbf{user=nom\_d'utilisateur} \\
\hspace{1mm} \textbf{password=votre\_mot\_de\_passe} \\
 
\hspace{1mm}  \textbf{Contrôle d'accès, étape 1: Vérification de la connexion}
\vspace{5mm} \\
\hspace{1mm}  Votre identité est basée sur 3 informations:
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{l'hôte depuis l'endroit où vous vous connectez}
\item[\hspace{1mm} -]{Votre nom d'utilisateur MySQL}
\item[\hspace{1mm} -]{Le mot de passe}
\end{description}
 
\hspace{1mm}  La vérification est réalisée avec les 3 colonnes de la table user [host, user et password]. host peut être un nom d'hôte ou une ip. Pour les ip on peut spécifier le masque:(spécifier une plage)
\vspace{5mm} \\
\hspace{1mm}  \textbf{GRANT ALL PRIVILEGES ON db.* TO david@'192.58.197.0/255.255.255.0';}
\vspace{5mm} \\
\hspace{1mm}  Caractères utilisable pour host: \% n'importe quel hôte, ou encore \textbf{x.y.\%}
\vspace{5mm} \\
\hspace{1mm}  Pour connaître sous quel utilisateur on est connecté: \textbf{SELECT CURRENT\_USER();}
\vspace{5mm} \\
 
 
\hspace{1mm}  \textbf{Contrôle d'accès, étape 2 : Vérification de la requête}
\vspace{5mm} \\
\hspace{1mm}  pour chaque requête, le serveur vérifie les droits. Ces droits peuvent provenir des tables user, db, tables\_priv, column\_priv. les droit sont vérifiés comme suit :
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} ]{droits globaux}
\item[\hspace{1mm} ]{OR (droits de base AND droits d'hôte)}
\item[\hspace{1mm} ]{OR droits de table}
\item[\hspace{1mm} ]{OR droits de colonne}
\end{description}
 
\hspace{1mm}  On devrait toujours tester les requêtes dans la table de droits, en utilisant l'utilitaire \textbf{mysqlaccess}.
\vspace{5mm} \\
\hspace{1mm}  Lors de la modification de privilèges, les droits \textbf{GRANT}, \textbf{REVOKE} et \textbf{PASSWORD} sont immédiatement pris en compte. Si on modifie les tables de droit manuellement avec \textbf{INSERT UPDATE} etc... on doit exécuter la commande \textbf{FLUSH PRIVILEGES} ou la commande \textbf{mysqladmin flush-privileges}, ou encore \textbf{mysqladmin reload}
\vspace{5mm} \\
\hspace{1mm}  syntaxe pour modifier le mot de passe :
\vspace{5mm} \\
\hspace{1mm}  \textbf{SET PASSWORD FOR 'abe'@'host\_name' = PASSWORD('eagle');}
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Gestion des comptes utilisateur }
 
\
 
\hspace{1mm}  Les noms d'utilisateurs MySQL peuvent avoir jusqu'à 16 caractères. Il y'a 2 manières d'ajouter de nouveaux comptes:
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Utiliser la commande GRANT}
\item[\hspace{1mm} -]{Manipuler la table des droits directement}
\end{description}
 
\hspace{1mm} exemples de syntaxe:
 
{\ttfamily
mysql> GRANT ALL PRIVILEGES ON generator.php TO 'monty'@'localhost'\\ -> IDENTIFIED BY 'un\_mot\_de\_passe' WITH GRANT OPTION ;\\ mysql> GRANT ALL PRIVILEGES ON generator.php TO 'monty'@'\%'\\ -> IDENTIFIED BY 'un\_mot\_de\_passe' WITH GRANT OPTION ;\\ mysql> GRANT RELOAD,PROCESS ON generator.php TO 'admin'@'localhost' ;\\ mysql> GRANT USAGE ON generator.php TO 'dummy'@'localhost' ;\\ mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('un\_mot\_de\_passe'),\\ -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') ;\\ mysql> INSERT INTO user VALUES('\%','monty',PASSWORD('un\_mot\_de\_passe'),\\ -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') ;\\ mysql> INSERT INTO user SET Host='localhost',User='admin',\\ -> Reload\_priv='Y', Process\_priv='Y' ;\\ mysql> INSERT INTO user (Host,User,Password)\\ -> VALUES('localhost','dummy','') ;\\ mysql> FLUSH PRIVILEGES ;\\ mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP\\ -> ON bankaccount.*\\ -> TO custom@localhost\\ -> IDENTIFIED BY 'stupid' ;\\ mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP\\ -> ON expenses.*\\ -> TO custom@whitehouse.gov\\ -> IDENTIFIED BY 'stupid' ;\\ mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP\\ -> ON customer.*\\ -> TO custom@'\%'\\ -> IDENTIFIED BY 'stupid' ;\\ mysql> INSERT INTO user (Host,User,Password)\\ -> VALUES('localhost','custom',PASSWORD('stupid')) ;\\ mysql> INSERT INTO user (Host,User,Password)\\ -> VALUES('server.domain','custom',PASSWORD('stupid')) ;\\ mysql> INSERT INTO user (Host,User,Password)\\ -> VALUES('whitehouse.gov','custom',PASSWORD('stupid')) ;\\ mysql> INSERT INTO db\\ -> (Host,Db,User,Select\_priv,Insert\_priv,Update\_priv,Delete\_priv,\\ -> Create\_priv,Drop\_priv)\\ -> VALUES\\ -> ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y') ;\\ mysql> INSERT INTO db\\ -> (Host,Db,User,Select\_priv,Insert\_priv,Update\_priv,Delete\_priv,\\ -> Create\_priv,Drop\_priv)\\ -> VALUES\\ -> ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y') ;\\ mysql> INSERT INTO db\\ -> (Host,Db,User,Select\_priv,Insert\_priv,Update\_priv,Delete\_priv,\\ -> Create\_priv,Drop\_priv)\\ -> VALUES('\%','customer','custom','Y','Y','Y','Y','Y','Y') ;\\ mysql> FLUSH PRIVILEGES ;\\
}
\vspace{8mm}
 
\hspace{1mm} Si vous voulez donner un accès spécifique à un utilisateur à partir de n'importe quelle machine d'un domaine donné, vous pouvez utiliser la commande GRANT, en utilisant ‘\%’ comme joker dans le nom de l'hôte \\
\hspace{1mm} \textbf{mysql> GRANT ...} \\
\hspace{1mm} \textbf{-> ON generator.php} \\
\hspace{1mm} \textbf{-> TO monutilisateur@"\%.mondomaine.com"} \\
\hspace{1mm} \textbf{-> IDENTIFIED BY 'monmotdepasse' ;} \\
\hspace{1mm} Pour faire la même chose en modifiant directement la table de droits, faites \\
\hspace{1mm} \textbf{mysql> INSERT INTO user VALUES ('\%.mondomaine.com', 'monutilisateur',} \\
\hspace{1mm} \textbf{-> PASSWORD('monmotdepasse'),...) ;} \\
\hspace{1mm} \textbf{mysql> FLUSH PRIVILEGES ;} \\
\hspace{1mm} Supprimer un compte utilisateur, utiliser la commande \\
\hspace{1mm} \textbf{DROP USER} \\
 
\vspace{1cm}{\huge Limiter les ressources utilisateur }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{nombre de requêtes par heure}
\item[\hspace{1mm} -]{nombre de modifications par heure}
\item[\hspace{1mm} -]{Nombre de connexions réalisées par heures}
\end{description}
 
\hspace{1mm} Pour configurer et changer les limites d'un compte existant, utiliser la commande GRANT USAGE au niveau global avec ON *.*. \\
\hspace{1mm} \textbf{mysql> GRANT USAGE ON generator.php TO 'francis'@'localhost'} \\
\hspace{1mm} \textbf{-> WITH MAX\_QUERIES\_PER\_HOUR 100 ; ; limite de requêtes à 100} \\
\hspace{1mm} \textbf{mysql> GRANT USAGE ON generator.php TO 'francis'@'localhost'} \\
\hspace{1mm} \textbf{-> WITH MAX\_CONNECTIONS\_PER\_HOUR 0 ; ; limite supprimée} \\
\hspace{1mm} Pour remettre à 0 les compteurs pour tous les comptes, faites \\
\hspace{1mm} \textbf{FLUSH USER\_RESOURCES} \\
\hspace{1mm} puis \\
\hspace{1mm} \textbf{FLUSH PRIVILEGES} \\
 
\vspace{1cm}{\huge Configurer les mots de passe }
 
\
 
\hspace{1mm} les mots de passe peuvent être assignés avec mysqladmin. \\
\hspace{1mm} \textbf{mysqladmin -u user\_name -h host\_name password "newpasswd"} \\
\hspace{1mm} On peut utiliser SET PASSWORD \\
\hspace{1mm} \textbf{SET PASSWORD FRO 'user\_name'@'\%' = PASSWORD('newpasswd') ;} \\
\hspace{1mm} modifier son propre mot de passe \\
\hspace{1mm} \textbf{SET PASSWORD = PASSWORD('newpasswd') ;} \\
\hspace{1mm} On peut utiliser GRANT USAGE au niveau global ON generator.php \\
\hspace{1mm} \textbf{GRANT USAGE ON generator.php TO 'user\_name'@'\%' IDENTIFIED BY 'newpasswd' ;} \\
\hspace{1mm} on peut utiliser la table user directement. Pour établir un mot de passe à la création d'un compte \\
\hspace{1mm} \textbf{INSERT INTO user (Host,User,Password) VALUES('\%','new\_user','newpasswd') ;} \\
\hspace{1mm} \textbf{FLUSH PRIVILEGES ;} \\
\hspace{1mm} Pour changer le mot de passe d'un compte existant \\
\hspace{1mm} \textbf{UPDATE user SET Password = PASSWORD('newpasswd') WHERE Host = '\%' AND User = 'user\_name' ;} \\
\hspace{1mm} \textbf{FLUSH PRIVILEGES ;} \\
\hspace{1mm} En utilisant mysqladmin password ou GRANT .....IDENTIFIED BY la fonction PASSWORD() n'est pas nécessaire. \\
\hspace{1mm} Il est possible de lister les mots de passe dans le groupe [client] de my.cnf. faire un chmod 400 /etc/mysql/my.cnf \\
 
\vspace{1cm}{\huge Connexions sécurisées }
 
\
 
\hspace{1mm}  MySQL n'utilise pas les connexions chiffrées par défaut, car cela ralentit considérablement le protocole de communication. Chiffrer les données est une tâche particulièrement coûteuse, qui peut ralentir considérablement les tâches principales de MySQL.
\vspace{5mm} \\
\hspace{1mm}  Pour utiliser les connexions sécurisées utiliser le script configure avec les options \textbf{--with-vio} et \textbf{--with-openssl}
\vspace{5mm} \\
\hspace{1mm}  Pour vérifier que le serveur supporte les connexions sécurisées utiliser la commande : \textbf{SHOW VARIABLES LIKE 'have\_openssl'}
\vspace{5mm} \\
 
 
\hspace{1mm} \textbf{DIR=`pwd`/openssl} \\
\hspace{1mm} \textbf{PRIV=\$DIR/private} \\
\hspace{1mm} \textbf{mkdir \$DIR \$PRIV \$DIR/newcerts} \\
\hspace{1mm} \textbf{cp /usr/share/ssl/openssl.cnf \$DIR} \\
\hspace{1mm} \textbf{replace ./demoCA \$DIR -- \$DIR/openssl.cnf} \\
\hspace{1mm} \# Créez les dossiers nécessaires : \$database, \$serial et \$new\_certs\_dir optionnel \\
\hspace{1mm} \textbf{touch \$DIR/index.txt} \\
\hspace{1mm} \textbf{echo "01" > \$DIR/serial} \\
\hspace{1mm} \# Génération du cerificat d'autorité (CA) \\
\hspace{1mm} \textbf{openssl req -new -x509 -keyout \$PRIV/cakey.pem -out \$DIR/cacert.pem -config \$DIR/openssl.cnf} \\
\hspace{1mm} \# Création des clé et requêtes serveur \\
\hspace{1mm} \textbf{openssl req -new -keyout \$DIR/server-key.pem -out \$DIR/server-req.pem -days 3600 -config \$DIR/openssl.cnf} \\
\hspace{1mm} \# Supprimez la passe-phrase de la clé (optionnel) \\
\hspace{1mm} \textbf{openssl rsa -in \$DIR/server-key.pem -out \$DIR/server-key.pem} \\
\hspace{1mm} \# Signez le certificat serveur \\
\hspace{1mm} \textbf{openssl ca -policy policy\_anything -out \$DIR/server-cert.pem -config \$DIR/openssl.cnf -infiles \$DIR/server-req.pem} \\
\hspace{1mm} \# Créez les clé et requêtes client \\
\hspace{1mm} \textbf{openssl req -new -keyout \$DIR/client-key.pem -out \$DIR/client-req.pem -days 3600 -config \$DIR/openssl.cnf} \\
\hspace{1mm} \# Supprimez la passe-phrase de la clé (optionnel) \\
\hspace{1mm} \textbf{openssl rsa -in \$DIR/client-key.pem -out \$DIR/client-key.pem} \\
\hspace{1mm} \# Signez le certificat client \\
\hspace{1mm} \textbf{openssl ca -policy policy\_anything -out \$DIR/client-cert.pem -config \$DIR/openssl.cnf -infiles \$DIR/client-req.pem} \\
\hspace{1mm} \# Créez le fichier my.cnf que vous pourrez utiliser pour tester les différents certificats \\
\hspace{1mm} \textbf{cnf=""} \\
\hspace{1mm} \textbf{cnf="\$cnf [client]"} \\
\hspace{1mm} \textbf{cnf="\$cnf ssl-ca=\$DIR/cacert.pem"} \\
\hspace{1mm} \textbf{cnf="\$cnf ssl-cert=\$DIR/client-cert.pem"} \\
\hspace{1mm} \textbf{cnf="\$cnf ssl-key=\$DIR/client-key.pem"} \\
\hspace{1mm} \textbf{cnf="\$cnf [mysqld]"} \\
\hspace{1mm} \textbf{cnf="\$cnf ssl-ca=\$DIR/cacert.pem"} \\
\hspace{1mm} \textbf{cnf="\$cnf ssl-cert=\$DIR/server-cert.pem"} \\
\hspace{1mm} \textbf{cnf="\$cnf ssl-key=\$DIR/server-key.pem"} \\
\hspace{1mm} \textbf{echo \$cnf | replace " " '} \\
\hspace{1mm} \textbf{' > \$DIR/my.cnf} \\
\hspace{1mm} \# To test MySQL \\
\hspace{1mm} \textbf{mysqld --defaults-file=\$DIR/my.cnf \&} \\
\hspace{1mm} \textbf{mysql --defaults-file=\$DIR/my.cnf} \\
\hspace{1mm} pour tester les connexions SSL, lancez le serveur comme ceci : \\
\hspace{1mm} \textbf{mysqld --default-file=/path/to/my.cnf \&} \\
\hspace{1mm} puis lancer le programme client en utilisant le même fichier d'options : \\
\hspace{1mm} \textbf{mysql --defaults-file=/path/to/my.cnf} \\
 
\vspace{1cm}{\huge Options de GRANT avec SSL }
 
\
 
\hspace{1mm} MySQL peut vérifier les certificats X509 en plus de la combinaison habituelle de nom d'utilisateur et mot de passe.<br>Différentes possibilités pour limiter les connexions: \\
 
\hspace{1mm} l'option REQUIRE SSL requière que les connexions soient chiffrées avec SSL \\
\hspace{1mm} \textbf{mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost} \\
\hspace{1mm} \textbf{-> IDENTIFIED BY "goodsecret" REQUIRE SSL ;} \\
\hspace{1mm} REQUIRE X509 impose au client d'avoir un certificat valide, mais le certificat lui même importe peu, la seul restriction est qu'il doit être possible de vérifier la signature avec une autorité de certification. \\
\hspace{1mm} \textbf{mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost} \\
\hspace{1mm} \textbf{-> IDENTIFIED BY "goodsecret" REQUIRE X509 ;} \\
\hspace{1mm} REQUIRE ISSUER “issuer” restreint les tentatives de connexions : le client doit se présenter avec un certificat X509 valide, émit par l'autorité de certification “issuer”. Utiliser un certificat x509 implique obligatoirement des chiffrements, donc l'option SSL est sous entendue. \\
\hspace{1mm} \textbf{mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost} \\
\hspace{1mm} \textbf{-> IDENTIFIED BY "goodsecret"} \\
\hspace{1mm} \textbf{-> REQUIRE ISSUER "C=FI, ST=Some-State, L=Helsinki,} \\
\hspace{1mm} \textbf{"> O=MySQL Finland AB, CN=Tonu Samuel/Email=tonu@mysql.com" ;} \\
\hspace{1mm} REQUIRE SUBJECT “subject” impose au client d'avoir un certificat X509 valide avec le sujet “subject” \\
\hspace{1mm} \textbf{mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost} \\
\hspace{1mm} \textbf{-> IDENTIFIED BY "goodsecret"} \\
\hspace{1mm} \textbf{-> REQUIRE SUBJECT "C=EE, ST=Some-State, L=Tallinn,} \\
\hspace{1mm} \textbf{"> O=MySQL demo client certificate,} \\
\hspace{1mm} \textbf{"> CN=Tonu Samuel/Email=tonu@mysql.com" ;} \\
\hspace{1mm} REQUIRE CIPHER “cipher” est utilisé pour s'assurer que les chiffrements sont suffisamment robuste, et que la bonne longueur de la clé est utilisée. \\
\hspace{1mm} \textbf{mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost} \\
\hspace{1mm} \textbf{-> IDENTIFIED BY "goodsecret"} \\
\hspace{1mm} \textbf{-> REQUIRE CIPHER "EDH-RSA-DES-CBC3-SHA" ;} \\
\hspace{1mm} Les options SUBJECT, ISSUER et CIPHER peuvent être combinées : \\
\hspace{1mm} \textbf{mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost} \\
\hspace{1mm} -> IDENTIFIED BY "goodsecret" \\
\hspace{1mm} -> REQUIRE SUBJECT "C=EE, ST=Some-State, L=Tallinn, \\
\hspace{1mm} "> O=MySQL demo client certificate, \\
\hspace{1mm} "> CN=Tonu Samuel/Email=tonu@mysql.com" \\
\hspace{1mm} -> AND ISSUER "C=FI, ST=Some-State, L=Helsinki, \\
\hspace{1mm} "> O=MySQL Finland AB, CN=Tonu Samuel/Email=tonu@mysql.com" \\
\hspace{1mm} -> AND CIPHER "EDH-RSA-DES-CBC3-SHA" ; \\
 
\vspace{1cm}{\huge Options SSL en ligne de commande }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} --ssl]{indique que le serveur autorise les connexions SSL, on doit spécifier également les options –ssl-ca ,–ssl-cert et –ssl-key}
\item[\hspace{1mm} --ssl-ca=file\_name]{chemin vers le fichier avec une liste des autorités de certifications SSL connus}
\item[\hspace{1mm} --ssl-capath=directory\_name]{le chemin où se trouve les certificats SSL au format PEM}
\item[\hspace{1mm} --ssl-cipher=file\_name]{nom du fichier de certificat SSL à utiliser pour établir une connexion sécurisée.}
\item[\hspace{1mm} --ssl-cipher=cipher\_list]{liste de chiffrements autorisées, à utiliser avec SSL a le même format que la commande openssl ciphers.}
\item[\hspace{1mm} --ssl-key=file\_name]{nom du fichier de la clé SSL à utiliser pour établir une connexion sécurisée.}
\end{description}
 
\vspace{1cm}{\huge Prévention des désastres et restauration }
 
\
 
\hspace{1mm}  Pour sauvegarder des bases de données:
\vspace{5mm} \\
\hspace{1mm}  Faire un \textbf{LOCK TABLES} sur les tables concernées, suivi d'un \textbf{FLUSH TABLES} pour celles-ci. On a besoin que d'un verrou en lecture, cela permet aux autres threads de continuer à effectuer des requêtes sur les tables dont on fait la copie des fichiers. \textbf{FLUSH TABLES} est requis pour s'assurer que toutes les pages d'index actifs soient écrits sur le disque avant de commencer la sauvegarde.
\vspace{5mm} \\
\hspace{1mm}  Pour faire une sauvegarde d'une table SQL, il suffit d'utiliser \textbf{SELECT INTO OUTFILE} ou \textbf{BACKUP TABLE}. On peut utiliser aussi \textbf{mysqldump} ou le script \textbf{mysqlhotcopy}.
\vspace{5mm} \\
 
 
\hspace{1mm} Effectuer une sauvegarde complète de la base de données: \\
\hspace{1mm} \textbf{mysqldump –tab=/path/to/backup –opt –all} \\
\hspace{1mm} ou \\
\hspace{1mm} \textbf{mysqlhotcopy base /path/to/backup} \\
 
\hspace{1mm}  Arrêtez mysqld et le redémarrer avec l'option \textbf{–log-update[=nom-fichier]}. Les fichiers de log fournissent les informations dont vous avez besoin pour répliquer les modifications de la base de données qui sont subséquents au moment où vous avez utilisé mysqldump.
\vspace{5mm} \\
\hspace{1mm}  Si votre serveur MySQL est un esclave, quelque soit la sauvegarde que vous utilisez, lorsque vous sauvez vos données sur votre esclave, vous devez aussi sauver les fichiers \textbf{master.info} et \textbf{relay-log.info}, qui sont nécessaires pour relancer la réplication après la restauration des données de l'esclave. Si votre esclave doit traiter des commandes \textbf{LOAD DATA INFILE}, vous devez aussi sauver les fichiers nommés \textbf{SQL\_LOAD-*}, qui sont dans le dossier spécifié par \textbf{--slave-load-tmpdir}. Ce dossier vaut par défaut la valeur de la variable tmpdir, si elle n'est pas spécifiée. L'esclave aura besoin de ces fichiers pour relancer la réplication d'une opération \textbf{LOAD DATA INFILE} interrompue.
\vspace{5mm} \\
 
 
\hspace{1mm}  Si vous avez besoin de restaurer quelque chose, essayez d'abord de restaurer vos tables avec \textbf{REPAIR TABLE} ou \textbf{myisamchk -r} en premier. Cela devrait fonctionner dans 99.9\% des cas. Si myisamchk ne réussi pas, essayez la procédure suivante (cela ne fonctionnera que si vous avez démarré MySQL avec \textbf{--log-update}:
\vspace{5mm} \\
 
 
\hspace{1mm} Restaurez la sauvegarde originale de mysqldump. \\
\hspace{1mm} Exécutez la commande suivante pour remettre en marche les mises à jour dans le log binaire: \\
\hspace{1mm} \textbf{shell> mysqlbinlog hostname-bin.[0-9]* | mysql} \\
 
\vspace{1cm}{\huge Politique de sauvegarde }
 
\
 
\hspace{1mm}  Faire une sauvegarde de toutes les tables InnoDB dans toutes les bases:
\vspace{5mm} \\
\hspace{1mm}  \textbf{shell> mysqldump --single-transaction --all-databases > backup\_sunday\_1\_PM.sql}
\vspace{5mm} \\
\hspace{1mm}  Le fichier .sql résultant, produit par mysqldump contient les commandes \textbf{SQL INSERT} qui peuvent être utilisées pour recharger les tables ultérieurement.
\vspace{5mm} \\
 
 
\hspace{1mm}  Pour effectuer des sauvegarde incrémentales, le serveur doit être lancé avec l'option \textbf{--log-bin} pour qu'il puisse stocker ces modifications au fur et à mesure des modifications des données. Cette option active le log binaire, ce qui fait que chaque commande qui modifie les données est enregistré dans un fichier appelé log binaire. A chaque fois que le serveur redémarre, MySQL créer un nouveau fichier de log binaire, en utilisant le numéro de séquence suivant.
\vspace{5mm} \\
\hspace{1mm}  Lorsque le serveur fonctionne on peut aussi lui dire de clore le fichier log et d'en ouvrir un nouveau avec la commande \textbf{SQL FLUSH LOGS} ou bien avec \textbf{mysqladmin flush-logs}. La commande \textbf{mysqldump} dispose aussi d'une option pour clore les fichiers de logs. Le fichier \textbf{.index} contient la liste de tous les fichiers de logs binaire du dossier de données. Ce fichier est utilisé durant les opérations de réplication.
\vspace{5mm} \\
 
 
\hspace{1mm} utiliser mysqldump pour qu'il referme les logs binaires lors de la sauvegarde complète, et que le fichier de sauvegarde contienne les noms des nouveaux fichiers de logs \\
\hspace{1mm} \textbf{shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > blackup\_sunday\_1\_PM.sql} \\
\hspace{1mm} pour supprimer les logs anciens, par exemple ceux qui sont antérieurs à la sauvegarde complète : \\
\hspace{1mm} \textbf{shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup\_sunday\_1\_PM.sql} \\
 
\hspace{1mm}  Noter que la commande est dangereuse si le serveur est un maitre réplicateur car les esclaves pourraient ne pas avoir traité le contenu des logs binaires. La description de la commande \textbf{PURGE MASTER LOGS} explique ce qui doit être vérifié avant d'effacer un fichier de log binaire.
\vspace{5mm} \\
 
 
\hspace{1mm} Utiliser les sauvegardes pour la restauration: \\
\hspace{1mm} \textbf{shell> mysql < blachup\_sunday\_1\_PM.sql} \\
\hspace{1mm} pour utiliser les sauvegardes incrémentales: \\
\hspace{1mm} \textbf{shell> mysqlbinlog mysqld-bin.000007 mysqld-bin.000008 | mysql} \\
\hspace{1mm} Il reste la dernière sauvegarde, qui est le dernier fichier de log binaire. de préférence spécifier un chemin vers un volume sécurisé pour l'option --log-bin. \\
 
\vspace{1cm}{\huge Fichiers de log de MySQL }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} Le log d'erreurs]{Problèmes rencontrés lors du démarrage, de l'exécution ou de l'arrêt de mysqld.}
\item[\hspace{1mm} Le log ISAM]{Garde une trace des changements liés au tables ISAM. Utilisé uniquement pour déboguer le code ISAM.}
\item[\hspace{1mm} Le log de requêtes]{Connexions établies et requêtes exécutées.}
\item[\hspace{1mm} Le log de mises à jour]{Désapprouvé : Enregistre toutes les commandes qui changent les données.}
\item[\hspace{1mm} Le log binaire]{Enregistre toutes les commandes qui changent quelque chose. Utilisé pour la réplication.}
\item[\hspace{1mm} Le log des requêtes lentes]{Enregistre toutes les requêtes qui ont pris plus de long\_query\_time à s'exécuter ou celles qui n'ont pas utilisé d'index.}
\end{description}
 
\hspace{1mm}  Par défaut, tous les fichiers de log peuvent être trouvés dans le dossier de données de mysqld.On peut forcer mysqld à rouvrir les fichiers de log ou à passer à un nouveau log en exécutant FLUSH LOGS.
\vspace{5mm} \\
\hspace{1mm}  Le log d'erreurs: Contiennent les informations indiquant quand mysqld a été lancé ou arrêté, ainsi que les erreurs critiques. si mysqld s'arrête inopinément -> restarted mysqld. Si mysqld remarque une table à réparer/analyser, il l'écrit également.
\vspace{5mm} \\
\hspace{1mm}  Pour spécifier l'emplacement du log d'erreur: \textbf{--log-error[=file\_name]}. Sans cette option, écrit sur la sortie standard.
\vspace{5mm} \\
\hspace{1mm}  Pour démarrer le log général de requêtes: \textbf{--log[=fichier]} (par défaut nommé 'hostname'.log)
\vspace{5mm} \\
\hspace{1mm}  Pour démarrer le log des requêtes lentes: \textbf{--log-slow-queries[=file\_name]}. Toutes les requêtes plus longues que \textbf{long\_query\_time} secondes à s'exécuter. Le temps d'acquisition d'un verrou n'est par compté. Pour obtenir un sommaire des requêtes lentes, utiliser mysqldumpslow.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Entretient des fichier de log }
 
\
 
\hspace{1mm}  \textbf{mysql-log-rotate} permet de gérer les logs. attention au logs pour la réplication.Pour forcer Mysql à utiliser de nouveaux fichiers de log: \textbf{mysqladmin flush-logs} ou \textbf{FLUSH-LOGS}.
\vspace{5mm} \\
\hspace{1mm}  \textbf{flush-logs} permet de fermer les logs puis les rouvre, donc il faut les déplacer avant.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Multiples serveurs sur une seule machine }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} ]{au minimum, les options suivantes doivent être différentes sur chaque serveur:}
\item[\hspace{1mm} --port=port\_num]{port d'écoute TCP}
\item[\hspace{1mm} --socket=path]{chemin du socket}
\item[\hspace{1mm} --pid-file=path]{chemin du fichier PID}
\item[\hspace{1mm} ]{Les options suivantes, si utilisées, doivent être différentes pour chaque serveur:}
\item[\hspace{1mm} --log=path]{}
\item[\hspace{1mm} --log-bin=path]{}
\item[\hspace{1mm} --log-update=path]{}
\item[\hspace{1mm} --log-error=path]{}
\item[\hspace{1mm} --log-isam=path]{}
\item[\hspace{1mm} --bdb-logdir=path]{}
\item[\hspace{1mm} ]{Pour de meilleurs performances, on pour également spécifier les options suivantes, pour répartir la charge entre plusieurs disques physiques}
\item[\hspace{1mm} ]{}
\item[\hspace{1mm} --tmpdir=path]{}
\item[\hspace{1mm} --bdb-tmpdir=path]{}
\item[\hspace{1mm} ]{Également, le dossier de données:}
\item[\hspace{1mm} ]{}
\item[\hspace{1mm} --datadir=path]{}
\item[\hspace{1mm} ]{En cas de plusieurs installations de MySQL à différents endroits, utiliser:}
\item[\hspace{1mm} ]{}
\item[\hspace{1mm} --base-dir=path]{}
\item[\hspace{1mm} ]{Pour spécifier de fichier d'option à utiliser:}
\item[\hspace{1mm} ]{}
\item[\hspace{1mm} --defauts-file]{}
\end{description}
 
\hspace{1mm} Pour lancer un serveur avec son fichier d'option \\
\hspace{1mm} \textbf{shell> mysqld --defaults-file=C :\textbackslash{}my-opts1.cnf} \\
\hspace{1mm} pour l'éteindre \\
\hspace{1mm} \textbf{shell> mysqladmin --port=3307 shutdown} \\
\hspace{1mm} On peut compiler mysql avec différents port et socket pour chacun avec \\
\hspace{1mm} \textbf{./configure --with-tcp-port=port\_number --with-unix-socket-path=file\_name --prefix=/usr/local/mysql-4.0.17} \\
\hspace{1mm} Connaître les caractéristiques d'un serveur \\
\hspace{1mm} \textbf{mysqladmin --host=host\_name --port=port\_number variables} \\
\hspace{1mm} Pour lancer un serveur sans l'avoir compilé avec des valeurs par défaut \\
\hspace{1mm} \textbf{/path/to/mysqld\_safe --socket=file\_name --port=port\_number} \\
\hspace{1mm} Pour utiliser un dossier de données différent, utiliser l'option \\
\hspace{1mm} \textbf{--datadir=path à mysqld\_safe} \\
\hspace{1mm} Idem, en utilisant les variables d'environnement pour spécifier le nom du socket et le port \\
\hspace{1mm} \textbf{shell> MYSQL\_UNIX\_PORT=/tmp/mysqld-new.sock} \\
\hspace{1mm} \textbf{shell> MYSQL\_TCP\_PORT=3307} \\
\hspace{1mm} \textbf{shell> export MYSQL\_UNIX\_PORT MYSQL\_TCP\_PORT} \\
\hspace{1mm} \textbf{shell> scripts/mysql\_install\_db} \\
\hspace{1mm} \textbf{shell> bin/mysqld\_safe \&} \\
\end{flushleft}
\end{document}
